package com.dats.util;

import com.dats.db.DatS;
import com.dats.pool.DatSPool;

import java.io.File;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author sig
 * @since 2016/10/5
 */
public class SqlUtil {

    public static void build(String parent, String name, String sql) {
        name = format(name);
        String fieldTmp = null;
        StringBuilder builder = new StringBuilder();
        List<String> list = ExcelParser.getStringList(
                parent + name + ".xlsx");

        for (String sqlStr : list) {

            if (null == sqlStr ||
                    "".equals(sqlStr.trim()))
                continue;
            String[] segments = sqlStr.split("\t");
            String field = segments[0];
            String value = segments[1];

            if ("".equals(field.trim()) ||
                    "".equals(value.trim()))
                continue;

            if ("field".equals(field)) continue;
            int type = (int) Double.parseDouble(value);
            String commentStr = null;

            if (2 < segments.length && !"".equals(segments[2])) {
                commentStr = segments[2];
            }

            if (3 < segments.length && !"".equals(segments[3])) {
                commentStr = commentStr + " " + segments[3];
            }
            String typeStr = getTypeString(type);

            if (null != typeStr &&
                    !sql.toLowerCase().contains("`" +
                            field.toLowerCase() + "`")) {
                builder.append("\n");
                builder.append("ALTER TABLE `").append(name)
                        .append("` ADD COLUMN `").append(field)
                        .append("`").append(typeStr);

                if (null != commentStr)
                    builder.append("COMMENT '").append(commentStr)
                            .append("'");

                if (null == fieldTmp) {
                    builder.append(" FIRST");
                } else {
                    builder.append(" AFTER `").append(fieldTmp).append("`");
                }
                builder.append(";\n");
            }
            fieldTmp = field;
        }
        String alias;

        if (name.startsWith("base")) {
            alias = DatSPool.baseAlias;
        } else {
            alias = DatSPool.userAlias;
        }
        List<String> sqlList = Arrays.asList(
                builder.toString().split(";[\r\n]+"));
        DatS.updateBatch(alias, sqlList);
        System.out.println(builder);
    }

    public static void build(String parent, String name,
                             boolean classShow, boolean addShow) {
        name = format(name);
        byte[] bytes = FileUtil.readFile(parent + "sql.txt");

        if (null == bytes) return;
        String sql = new String(bytes, Charset.forName("UTF-8"));
        sql = sql.replaceAll("name", name);
        String fieldTmp = null;
        StringBuilder classBuilder = new StringBuilder("\n");
        StringBuilder sqlBuilder = new StringBuilder();
        StringBuilder addBuilder = new StringBuilder("\n");
        List<String> list = ExcelParser
                .getStringList(parent + name + ".xlsx");

        for (String sqlStr : list) {

            if (null == sqlStr ||
                    "".equals(sqlStr.trim()))
                continue;
            String[] segments = sqlStr.split("\t");
            String field = segments[0];
            String value = segments[1];

            if ("".equals(field.trim()) ||
                    "".equals(value.trim()))
                continue;

            if ("field".equals(field)) continue;
            int type = (int) Double.parseDouble(value);
            String commentStr = null;

            if (2 < segments.length && !"".equals(segments[2]))
                commentStr = segments[2];

            if (3 < segments.length && !"".equals(segments[3])) {
                commentStr += " " + segments[3];
            }
            String fieldStr = getFieldString(type);

            if (null != fieldStr) {
                classBuilder.append(fieldStr).append(field).append(";");

                if (null != commentStr)
                    classBuilder.append(" // ").append(commentStr);
                classBuilder.append("\n");
            }
            String typeStr = getTypeString(type);

            if (null != typeStr) {
                sqlBuilder.append("  `").append(field)
                        .append("`").append(typeStr);

                if (null != commentStr)
                    sqlBuilder.append("COMMENT '")
                            .append(commentStr).append("'");
                sqlBuilder.append(",\n");
                addBuilder.append("\n");
                addBuilder.append("ALTER TABLE `").append(name)
                        .append("` DROP COLUMN `").append(field)
                        .append("`;\n");
                addBuilder.append("ALTER TABLE `").append(name)
                        .append("` ADD COLUMN `").append(field)
                        .append("`").append(typeStr);

                if (null != commentStr)
                    addBuilder.append("COMMENT '")
                            .append(commentStr).append("'");

                if (null == fieldTmp) {
                    addBuilder.append(" FIRST");
                } else {
                    addBuilder.append(" AFTER `").append(fieldTmp).append("`");
                }
                addBuilder.append(";\n");
            }
            fieldTmp = field;
        }
        String alias;

        if (name.startsWith("base")) {
            sql = sql.replaceAll(
                    "  `del`[^\r^\n]+[\r\n]+",
                    sqlBuilder.toString());
            alias = DatSPool.baseAlias;
        } else {
            sql = sql.replaceAll(
                    "  `del`", sqlBuilder.toString() + "  `del`");
            alias = DatSPool.userAlias;
        }
        System.out.println(sql);
        List<String> sqlList = Arrays.asList(
                sql.split(";[\r\n]+"));
        DatS.updateBatch(alias, sqlList);

        if (classShow) System.out.println(classBuilder);

        if (addShow) System.out.println(addBuilder);
    }

    private static String getFieldString(int type) {
        String string = null;

        switch (type) {
            case 1:
                string = "\tprivate byte ";
                break;
            case 2:
                string = "\tprivate short ";
                break;
            case 3:
                string = "\tprivate int ";
                break;
            case 4:
                string = "\tprivate long ";
                break;
            case 5:
                string = "\tprivate float ";
                break;
            case 6:
                string = "\tprivate double ";
                break;
            case 7:
            case 8:
            case 9:
            case 10:
                string = "\tprivate String ";
                break;
            default:
                break;
        }
        return string;
    }

    private static String getTypeString(int type) {
        String string = null;

        switch (type) {
            case 1:
                string = " tinyint(4) NOT NULL ";
                break;
            case 2:
                string = " smallint(6) NOT NULL ";
                break;
            case 3:
                string = " int(11) NOT NULL ";
                break;
            case 4:
                string = " bigint(20) NOT NULL ";
                break;
            case 5:
                string = " float NOT NULL ";
                break;
            case 6:
                string = " double NOT NULL ";
                break;
            case 7:
                string = " varchar(30) NOT NULL DEFAULT '' ";
                break;
            case 8:
                string = " varchar(60) NOT NULL DEFAULT '' ";
                break;
            case 9:
                string = " varchar(255) NOT NULL DEFAULT '' ";
                break;
            case 10:
                string = " text NOT NULL ";
                break;
            default:
                break;
        }
        return string;
    }

    public static String format(String tableName) {
        if (tableName.startsWith("C_") || tableName.startsWith("c_"))
            tableName = tableName.substring(2);
        return tableName.toLowerCase();
    }

    public static List<String> getFileList(String parent) {
        List<String> list = new ArrayList<>();
        File directory = new File(parent);

        if (directory.exists() && directory.isDirectory()) {
            File[] files = directory.listFiles();

            if (null == files) return list;

            for (File file : files) {

                if (null == file) continue;
                String name = file.getName();

                if (!name.contains(".xlsx")) continue;
                name = name.replace(".xlsx", "");
                list.add(name);
            }
        }
        return list;
    }
}
